﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTOQLDV;
using System.Data.OleDb;
using System.Data;

namespace DAOQLDV
{
    public class DAODuLieu
    {
        public static List<DTODuLieu> LayDanhSachDuLieu()
        {
            List<DTODuLieu> dsDL = new List<DTODuLieu>();
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                String sql = "Select * From DuLieu";
                OleDbDataReader dr = da.ExecuteQuery(sql);
                while (dr.Read())
                {
                    DTODuLieu dtoDL = new DTODuLieu();
                    dtoDL.MaDuLieu = (int)dr["MaDuLieu"];
                    dtoDL.MaDichVu = (int)dr["MaDichVu"];
                    dtoDL.MaDuong = (int)dr["MaDuong"];
                    dtoDL.MaPhuong = (int)dr["MaPhuong"];
                    dtoDL.MaQuanHuyen = (int)dr["MaQuanHuyen"];
                    dtoDL.MaDiaDiem = (int)dr["MaTenDiaDiem"];
                    dtoDL.MaTinhThanh = (int)dr["MaTinhThanh"];
                    dtoDL.SoNha = dr["SoNha"].ToString();
                    dtoDL.ViDo = dr["ViDo"].ToString();
                    dtoDL.KinhDo = dr["KinhDo"].ToString();
                    dtoDL.ChuThich = dr["ChuThich"].ToString();

                    dsDL.Add(dtoDL);
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return dsDL;
        }
        public static bool ThemDuLieu(DTODuLieu dtoDL)
        {
            bool kq = false;
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                String sql = "Insert Into DuLieu(MaDichVu, MaTenDiaDiem, SoNha, MaDuong, MaPhuong, MaQuanHuyen, MaTinhThanh, KinhDo, ViDo, ChuThich) Values (";
                sql += dtoDL.MaDichVu + ", " + dtoDL.MaDiaDiem + ", '" + dtoDL.SoNha + "', " + dtoDL.MaDuong + ", " + dtoDL.MaPhuong + ", " + dtoDL.MaQuanHuyen + ", " + dtoDL.MaTinhThanh + ", '" + dtoDL.KinhDo + "', '" + dtoDL.ViDo + "', '" + dtoDL.ChuThich + "')";
                da.ExecuteNonQuery(sql);
                sql = "Select @@IDENTITY";
                dtoDL.MaDuLieu = (int)da.ExecuteScalar(sql);
                kq = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }
        public static bool XoaDuLieu(DTODuLieu dtoDL)
        {
            bool kq = false;
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                String sql = "Delete From DuLieu Where MaDuLieu = " + dtoDL.MaDuLieu;
                da.ExecuteNonQuery(sql);
                kq = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }        
        public static bool ThemDanhSach(List<DTODuLieu> dsdtoDL)
        {
            bool kq = false;
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                da.BeginTran();
                String sql = "";
                foreach (DTODuLieu dtoDL in dsdtoDL)
                {
                    da.ExecuteNonQuery(sql);
                }
                da.CommitTran();
                kq = true;
            }
            catch (Exception ex)
            {
                da.RollBackTran();
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }
        public static bool CapNhatDuLieu(DTODuLieu dtoDL)
        {
            bool kq = false;
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                //da.BeginTran();               
                String sql = "Update DuLieu Set MaDichVu=" + dtoDL.MaDichVu;
                sql += ", SoNha='" + dtoDL.SoNha + "', MaDuong=" + dtoDL.MaDuong + ", MaPhuong=" + dtoDL.MaPhuong;
                sql += ", MaQuanHuyen=" + dtoDL.MaQuanHuyen + ", MaTinhThanh=" + dtoDL.MaTinhThanh + ", KinhDo='" + dtoDL.KinhDo + "', ViDo='" + dtoDL.ViDo + "', ChuThich='" + dtoDL.ChuThich + "'";
                sql += " Where MaDuLieu = " + dtoDL.MaDuLieu;
                da.ExecuteNonQuery(sql);
                //da.CommitTran();
                kq = true;
            }
            catch (Exception ex)
            {
                //da.RollBackTran();
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }        
        public static bool CapNhatDanhSach(List<DTODuLieu> dsDuLieu)
        {
            bool kq = false;
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                da.BeginTran();
                String sql = "";
                foreach (DTODuLieu dtoDL in dsDuLieu)
                {
                    sql = "Update DuLieu Set MaDichVu=" + dtoDL.MaDichVu;
                    sql += ", SoNha='" + dtoDL.SoNha + "', MaDuong=" + dtoDL.MaDuong + ", MaPhuong=" + dtoDL.MaPhuong;
                    sql += ", MaQuanHuyen=" + dtoDL.MaQuanHuyen + ", MaTinhThanh=" + dtoDL.MaTinhThanh + ", KinhDo='" + dtoDL.KinhDo + "', ViDo='" + dtoDL.ViDo + "', ChuThich='" + dtoDL.ChuThich + "'";
                    sql += " Where MaDuLieu = " + dtoDL.MaDuLieu;
                    da.ExecuteNonQuery(sql);
                }
                da.CommitTran();
                kq = true;
            }
            catch (Exception ex)
            {
                da.RollBackTran();
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }
        public static bool XoaDanhSach(List<DTODuLieu> dsDuLieu)
        {
            bool kq = false;
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                da.BeginTran();
                foreach (DTODuLieu dtoDL in dsDuLieu)
                {
                    XoaDuLieu(dtoDL);
                }
                da.CommitTran();
                kq = true;
            }
            catch (Exception ex)
            {
                da.RollBackTran();
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }
        public static bool ThemDanhSachDuLieu(List<DTODuLieu> dsDuLieu)
        {
            bool kq = false;
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                da.BeginTran();
                foreach (DTODuLieu dtoDL in dsDuLieu)
                {
                    try
                    {
                        String sql = "Insert Into DuLieu(MaDichVu, MaTenDiaDiem, SoNha, MaDuong, MaPhuong, MaQuanHuyen, MaTinhThanh, KinhDo, ViDo, ChuThich) Values (";
                        sql += dtoDL.MaDichVu + ", " + dtoDL.MaDiaDiem + ", '" + dtoDL.SoNha + "', " + dtoDL.MaDuong + ", " + dtoDL.MaPhuong + ", " + dtoDL.MaQuanHuyen + ", " + dtoDL.MaTinhThanh + ", '" + dtoDL.KinhDo + "', '" + dtoDL.ViDo + "', '" + dtoDL.ChuThich + "')";
                        da.ExecuteNonQuery(sql);
                        sql = "Select @@IDENTITY";
                        dtoDL.MaDuLieu = (int)da.ExecuteScalar(sql);
                        SESSION._dsDuLieu.Add(dtoDL);
                    }
                    catch (ExecutionEngineException ex)
                    {
                        throw ex;
                    }
                }
                da.CommitTran();
                kq = true;
            }
            catch (Exception ex)
            {
                da.RollBackTran();
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }
    }
}
